The purpose of this project is to aid investors in making the best investment choices in the stock market. Our database provides the necessary financial data that an investor can find highly useful to evaluate a company’s financial health and wealth.
Problem Description
Problem domain: Creating a database with this data would address a problem faced by investors and financial analysts. Companies can have different means of reporting which makes it inefficient and time-consuming to look at.
Need: A database is needed to store and organize financial information and is useful for quicker decision-making processes and better management of all the existing data.
Entity-relationship diagrams
Chen diagram
Figure 1: Financial Data Manager in Chen Notation
Crows foot diagram:
erDiagram
COMPANY {
string ticker
string name
string sector
string industry
}
INCOME_STATEMENT {
int statement_id1
string ticker
date fiscal_year_end
float total_revenue
float cost_of_revenue
float gross_profit
float operating_income
float Net_income_common_stockholders
float income_before_tax
float ebit
float ebitda
}
STOCK {
string ticker
int company_id
float price
date IPO_date
}
BALANCE_SHEET {
string ticker
int statement_id2
date fiscal_year_end
float total_assets
float current_assets
float cash
float cash_and_cash_equivalents
float accounts_receivables
float total_debt
float net_tangible_assets
float long_term_debt
float working_capital
float invested_capital
float tangible_book_value
float total_capitalization
int shares_issued
float stockholders_equity
float retained_earnings
float common_stock_equity
}
CASH_FLOW {
int statement_id3
string ticker
date fiscal_year_end
float operating_cashflow
float capital_expenditures
float free_cash_flow
float cash_dividends_paid
}
EARNINGS {
int earnings_id
string ticker
int earnings_id
int company_id
date fiscal_date_end
float reported_eps
}
COMPANY ||--o{ INCOME_STATEMENT : publishes
COMPANY ||--o{ STOCK : issues
COMPANY ||--o{ BALANCE_SHEET : records
COMPANY ||--o{ CASH_FLOW : flows
COMPANY ||--o{ EARNINGS : reports
Screenshots
Image 1 - Main Screen
This image displays the default page of our application.
Image 2 - Creating a New Record
This image shows the web interface screen for creating a new record. In this example, it is creating a new record in the ‘Company’ table.
Image 3 - Editing an Existing Record
This image shows the web interface screen for updating an existing record. In this example, it is editing ‘Eod_price’ attribute of a record in the ‘Stock’ table.
Image 4 - Deleting an Existing Record
This image shows that records can be deleted using its primary key. In this example, it is deleting a record from the ‘Stock’ table, and its stock ticker is ‘AAPL’.
Image 5 - Running a Query
This image shows how the menu of queries can be ran. There is a dropdown selection of all possible reports to view. In this example, the first query is ran, and it shows all records in the ‘Company’ table and their corresponding tickers.
Image 6 - Filtering a Table
This image shows how each table can be filtered. in this example, I wanted to see all records in the Technology sector.
Reports
SQL DDL
No errors detected while loading: ./my-ddl.sql
20 queries:
List all companies and their stock tickers.
sql ="""SELECT Name, Ticker FROM Company;"""run_sql_and_return_df(sql, cnx)
Name
Ticker
0
Apple Inc.
AAPL
1
Amazon.com Inc.
AMZN
2
Alphabet Inc.
GOOGL
3
Meta Platforms Inc.
META
4
Microsoft Corporation
MSFT
5
Nvidia Corporation
NVDA
6
test
test
7
Tesla Inc.
TSLA
Find all companies from the Technology sector.
sql ="""SELECT Name FROM Company WHERE Sector = 'Technology';"""run_sql_and_return_df(sql, cnx)
Name
0
Apple Inc.
1
Microsoft Corporation
2
Nvidia Corporation
Get the stock ticker and end of day price for Apple.
sql ="""SELECT Stock_ticker, Eod_price FROM Stock WHERE Stock_ticker = 'AAPL';"""run_sql_and_return_df(sql, cnx)
Stock_ticker
Eod_price
0
AAPL
242.84
Find the companies with IPO dates after 2010
sql ="""SELECT Company.Name, Stock.IPO_dateFROM StockJOIN Company ON Stock.Stock_ticker = Company.TickerWHERE Stock.IPO_date > '2010-01-01'ORDER BY Stock.IPO_date;"""run_sql_and_return_df(sql, cnx)
Name
IPO_date
0
Tesla Inc.
2010-06-29
1
Meta Platforms Inc.
2012-05-18
Retrieve the total revenue and net income for Apple
sql ="""SELECT Total_revenue, Net_income_common_stockholders FROM Income_Statement WHERE Stock_ticker = (SELECT Stock_ticker FROM Company WHERE Ticker = 'AAPL');"""run_sql_and_return_df(sql, cnx)
Total_revenue
Net_income_common_stockholders
0
182527000.0
40269000.0
1
386064000.0
21331000.0
2
31536000.0
690000.0
3
85965000.0
29146000.0
4
365817000.0
94680000.0
5
168088000.0
61271000.0
6
257637000.0
76033000.0
7
469822000.0
33364000.0
8
16675000.0
4332000.0
9
53823000.0
5524000.0
10
117929000.0
39370000.0
11
394328000.0
99803000.0
12
198270000.0
72738000.0
13
282836000.0
59972000.0
14
513983000.0
-2722000.0
15
26914000.0
9752000.0
16
81462000.0
12583000.0
17
116609000.0
23200000.0
18
383285000.0
96995000.0
19
211915000.0
72361000.0
20
307394000.0
73795000.0
21
574785000.0
30425000.0
22
26974000.0
4368000.0
23
96773000.0
14999000.0
24
134902000.0
39098000.0
25
391035000.0
93736000.0
26
245122000.0
88136000.0
27
60922000.0
29760000.0
Get the total debt for all companies in the Consumer Cyclical sector with fiscal year end
sql ="""SELECT Company.Name, Balance_Sheet.Total_debt, Balance_Sheet.Fiscal_year_endFROM Company JOIN Stock ON Company.Ticker = Stock.Stock_tickerJOIN Balance_Sheet ON Stock.Stock_ticker = Balance_Sheet.Stock_tickerWHERE Company.Sector = 'Consumer Cyclical'ORDER BY Balance_Sheet.Fiscal_year_end ASC; """run_sql_and_return_df(sql, cnx)
Name
Total_debt
Fiscal_year_end
0
Amazon.com Inc.
84389000.0
2020-12-31
1
Tesla Inc.
13279000.0
2020-12-31
2
Amazon.com Inc.
116395000.0
2021-12-31
3
Tesla Inc.
8873000.0
2021-12-31
4
Amazon.com Inc.
140118000.0
2022-12-31
5
Tesla Inc.
5748000.0
2022-12-31
6
Amazon.com Inc.
135611000.0
2023-12-31
7
Tesla Inc.
9573000.0
2023-12-31
List all companies with a gross profit greater than 50 million.
sql ="""SELECT Company.Name, Income_Statement.Gross_profit, Income_Statement.Fiscal_year_endFROM CompanyJOIN Income_Statement ON Company.Ticker = Income_Statement.Stock_TickerWHERE Income_Statement.Gross_profit > 50000000ORDER BY Income_Statement.Fiscal_year_end ASC;"""run_sql_and_return_df(sql, cnx)
Name
Gross_profit
Fiscal_year_end
0
Amazon.com Inc.
51500000.0
2020-12-31
1
Meta Platforms Inc.
69273000.0
2020-12-31
2
Alphabet Inc.
97795000.0
2020-12-31
3
Microsoft Corporation
52232000.0
2021-06-30
4
Apple Inc.
152836000.0
2021-09-30
5
Alphabet Inc.
110939000.0
2021-12-31
6
Amazon.com Inc.
66315000.0
2021-12-31
7
Meta Platforms Inc.
95280000.0
2021-12-31
8
Microsoft Corporation
135620000.0
2022-06-30
9
Apple Inc.
170782000.0
2022-09-30
10
Alphabet Inc.
156633000.0
2022-12-31
11
Meta Platforms Inc.
91360000.0
2022-12-31
12
Amazon.com Inc.
67640000.0
2022-12-31
13
Microsoft Corporation
146052000.0
2023-06-30
14
Apple Inc.
169148000.0
2023-09-30
15
Alphabet Inc.
174062000.0
2023-12-31
16
Amazon.com Inc.
93805000.0
2023-12-31
17
Meta Platforms Inc.
108943000.0
2023-12-31
18
Microsoft Corporation
171008000.0
2024-06-30
19
Apple Inc.
180683000.0
2024-09-30
Find the companies with total assets greater than 1 billion.
sql ="""SELECT Company.Name, Balance_Sheet.Fiscal_year_endFROM CompanyJOIN Balance_Sheet ON Company.Ticker = Balance_Sheet.Stock_tickerWHERE Balance_Sheet.Total_assets > 1000000000ORDER BY Balance_Sheet.Fiscal_year_end ASC;"""run_sql_and_return_df(sql, cnx)
Name
Fiscal_year_end
0
no records returned
Get the fiscal year-end date for Microsoft from 2021 to 2024.
sql ="""SELECT Company.Name, Income_Statement.Fiscal_year_endFROM CompanyJOIN Income_Statement ON Company.Ticker = Income_Statement.Stock_tickerWHERE Company.Name = 'Microsoft Corporation'ORDER BY Income_Statement.Fiscal_year_end;"""run_sql_and_return_df(sql, cnx)
Name
Fiscal_year_end
0
Microsoft Corporation
2021-06-30
1
Microsoft Corporation
2022-06-30
2
Microsoft Corporation
2023-06-30
3
Microsoft Corporation
2024-06-30
Find all companies that have a working capital greater than 50 million.
sql ="""SELECT Company.Name, Balance_Sheet.Working_capitalFROM Company JOIN Balance_Sheet ON Balance_Sheet.Stock_Ticker = Company.TickerWHERE Balance_Sheet.Working_capital > 50000000ORDER BY Balance_Sheet.Working_capital"""run_sql_and_return_df(sql, cnx)
Name
Working_capital
0
Meta Platforms Inc.
53405000.0
1
Meta Platforms Inc.
60689000.0
2
Microsoft Corporation
74602000.0
3
Microsoft Corporation
80108000.0
4
Alphabet Inc.
89716000.0
5
Alphabet Inc.
95495000.0
6
Microsoft Corporation
95749000.0
7
Alphabet Inc.
117462000.0
8
Alphabet Inc.
123889000.0
Get the names of companies in the ‘Software Infrastructure’ industry.
sql ="""SELECT Name, Industry FROM Company WHERE Industry = 'Software Infrastructure';"""run_sql_and_return_df(sql, cnx)
Name
Industry
0
Microsoft Corporation
Software Infrastructure
Retrieve the total revenue and cost of revenue for Tesla.
sql ="""SELECT Company.Name, Income_Statement.Total_revenue, Income_Statement.Cost_of_revenue FROM CompanyJOIN Income_Statement ON Income_Statement.Stock_Ticker = Company.Ticker WHERE Company.Ticker = (SELECT Ticker FROM Company WHERE Ticker = 'TSLA');"""run_sql_and_return_df(sql, cnx)
Name
Total_revenue
Cost_of_revenue
0
Tesla Inc.
31536000.0
24906000.0
1
Tesla Inc.
53823000.0
40217000.0
2
Tesla Inc.
81462000.0
60609000.0
3
Tesla Inc.
96773000.0
79113000.0
Find companies that have both net income for common stockholders greater than 10 million.
sql ="""SELECT Company.Name, Income_Statement.Net_income_common_stockholdersFROM CompanyJOIN Income_Statement ON Income_Statement.Stock_Ticker = Company.Ticker WHERE Income_Statement.Net_income_common_stockholders > 10000000;"""run_sql_and_return_df(sql, cnx)
Name
Net_income_common_stockholders
0
Alphabet Inc.
40269000.0
1
Amazon.com Inc.
21331000.0
2
Meta Platforms Inc.
29146000.0
3
Apple Inc.
94680000.0
4
Microsoft Corporation
61271000.0
5
Alphabet Inc.
76033000.0
6
Amazon.com Inc.
33364000.0
7
Meta Platforms Inc.
39370000.0
8
Apple Inc.
99803000.0
9
Microsoft Corporation
72738000.0
10
Alphabet Inc.
59972000.0
11
Tesla Inc.
12583000.0
12
Meta Platforms Inc.
23200000.0
13
Apple Inc.
96995000.0
14
Microsoft Corporation
72361000.0
15
Alphabet Inc.
73795000.0
16
Amazon.com Inc.
30425000.0
17
Tesla Inc.
14999000.0
18
Meta Platforms Inc.
39098000.0
19
Apple Inc.
93736000.0
20
Microsoft Corporation
88136000.0
21
Nvidia Corporation
29760000.0
Retrieve the operating cashflow for Nvidia.
sql ="""SELECT Company.Name, Cash_Flow.Operating_cashflowFROM CompanyJOIN Cash_Flow ON Cash_Flow.Stock_Ticker = Company.TickerWHERE Company.Ticker = (SELECT Ticker FROM Company WHERE Ticker = 'NVDA');"""run_sql_and_return_df(sql, cnx)
Name
Operating_cashflow
0
Nvidia Corporation
5822000.0
1
Nvidia Corporation
9108000.0
2
Nvidia Corporation
5641000.0
3
Nvidia Corporation
28090000.0
Retreive the earnings for all tickers in the fiscal date end of 2022-12-31
Retrieve the stock ticker with fiscal year and cash dividends paid which are not null
sql ="""SELECT Company.Name, Cash_Flow.Cash_dividends_paid, Cash_Flow.Fiscal_year_endFROM CompanyJOIN Cash_Flow ON Cash_Flow.Stock_Ticker = Company.TickerWHERE Cash_Flow.Cash_dividends_paid IS NOT NULLORDER BY Cash_Flow.Fiscal_year_end"""run_sql_and_return_df(sql, cnx)
Name
Cash_dividends_paid
Fiscal_year_end
0
Nvidia Corporation
-395000.0
2021-01-31
1
Microsoft Corporation
-16521000.0
2021-06-30
2
Apple Inc.
-14467000.0
2021-09-30
3
Nvidia Corporation
-399000.0
2022-01-31
4
Microsoft Corporation
-18135000.0
2022-06-30
5
Apple Inc.
-14841000.0
2022-09-30
6
Nvidia Corporation
-398000.0
2023-01-31
7
Microsoft Corporation
-19800000.0
2023-06-30
8
Apple Inc.
-15025000.0
2023-09-30
9
Nvidia Corporation
-395000.0
2024-01-31
10
Microsoft Corporation
-21771000.0
2024-06-30
11
Apple Inc.
-15234000.0
2024-09-30
Retrieve the stock ticker and free cash flow where free cash flow is greater than 50,000,000